﻿//--------------------------------------------------------------
//
//作    者：蒙孝宗
//功能描述：数据库操作辅助类
//创建时间：2012-02-12
//修改记录：
//版    本：V1.0
//
//--------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using DbHelper.Driver;

namespace DbHelper.Data
{
    sealed class DbProvider : IProvider, ITransaction
    {
        #region Field

        /// <summary>
        /// 事务数量
        /// </summary>
        private int transCount = 0;
        /// <summary>
        /// 缓存表结构
        /// </summary>
        private static Dictionary<string, DataTable> dtSchema = new Dictionary<string, DataTable>();
        /// <summary>
        /// 当前使用的连接器
        /// </summary>
        private DbDriver connector;

        #endregion

        #region initialize

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbDriver"></param>
        public DbProvider(DbDriver dbDriver)
        {
            this.connector = dbDriver;
        }

        #endregion

        #region Property

        /// <summary>
        /// 连接数据库的对象
        /// </summary>
        public IDbConnection DbConnection
        {
            get { return connector.DbConnection; }
        }

        /// <summary>
        /// 执行 SQL 语句或存储过程的对象
        /// </summary>
        public IDbCommand DbCommand
        {
            get { return connector.DbCommand; }
        }

        /// <summary>
        /// 数据库适配器
        /// </summary>
        public IDbDataAdapter DbDataAdapter
        {
            get { return connector.DbDataAdapter; }
        }

        #endregion

        #region Open and Close

        /// <summary>
        /// 打开数据库
        /// </summary>
        public void Open()
        {
            if (DbConnection.State == ConnectionState.Closed ||
                DbConnection.State == ConnectionState.Broken)
                DbConnection.Open();
        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        public void Close()
        {
            //嵌套事务时，内外层事务同用同一个数据库服务对象
            //因此内层事务 Close 不能关闭数据库操作对象，外层事务还在使用
            if (TransCount > 0) return;

            if (DbConnection.State == ConnectionState.Open)
            {
                DbConnection.Close();
            }
        }

        #endregion

        #region Transaction

        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTransaction()
        {
            if (DbCommand.Transaction == null)
            {
                this.Open();
                DbCommand.Transaction = DbConnection.BeginTransaction();
            }
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void Commit()
        {
            DbCommand.Transaction.Commit();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            DbCommand.Transaction.Rollback();
        }

        /// <summary>
        /// 事务数量
        /// </summary>
        public int TransCount
        {
            get { return transCount; }
            set { transCount = value; }
        }

        #endregion

        #region Execute

        /// <summary>
        /// 执行 SQL 语句
        /// </summary>
        /// <param name="cmdText">SQL 语句</param>
        /// <returns></returns>
        public int Execute(string cmdText)
        {
            return this.Execute(CommandType.Text, cmdText, null);
        }

        /// <summary>
        /// 执行 SQL 语句或存储过程
        /// </summary>
        /// <param name="cmdType">SQL 类型</param>
        /// <param name="cmdText">SQL 语句或存储过程</param>
        /// <param name="paraValues">参数</param>
        /// <returns></returns>
        public int Execute(CommandType cmdType, string cmdText, params IDataParameter[] paraValues)
        {
            int result = this.Execute<int>(cmdType, cmdText, paraValues,
                            delegate()
                            {
                                return DbCommand.ExecuteNonQuery();
                            });

            return result;
        }

        /// <summary>
        /// 执行 SQL 语句
        /// </summary>
        /// <param name="cmdText">SQL 语句</param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText)
        {
            return this.ExecuteScalar(CommandType.Text, cmdText, null);
        }

        /// <summary>
        /// 执行 SQL 语句或存储过程
        /// </summary>
        /// <param name="cmdType">SQL 类型</param>
        /// <param name="cmdText"> SQL 语句或存储过程</param>
        /// <param name="paraValues">参数</param>
        /// <returns></returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText, params IDataParameter[] paraValues)
        {
            object result = this.Execute<object>(cmdType, cmdText, paraValues,
                                delegate()
                                {
                                    return DbCommand.ExecuteScalar();
                                });

            return result;
        }

        /// <summary>
        /// 执行 SQL 具体的方法
        /// </summary>
        /// <typeparam name="T">返回值类型</typeparam>
        /// <param name="cmdType">SQL 类型</param>
        /// <param name="cmdText">SQL 语句或存储过程</param>
        /// <param name="paraValues">参数</param>
        /// <param name="execute">执行方法</param>
        /// <returns></returns>
        private T Execute<T>(CommandType cmdType, string cmdText, IDataParameter[] paraValues, ExecuteHandler<T> execute)
        {
            PrepareCommand(cmdType, cmdText);
            PrepareParameters(paraValues);

            T result = default(T);

            try
            {
                Open();
                result = execute();
            }
            finally
            {
                ClearParameters();
            }

            return result;
        }

        //准备 SQL
        private void PrepareCommand(CommandType cmdType, string cmdText)
        {
            DbCommand.CommandType = cmdType;
            DbCommand.CommandText = cmdText;
        }

        //准备参数
        private void PrepareParameters(IDataParameter[] paraValues)
        {
            ClearParameters();
            if (paraValues != null)
            {
                foreach (IDataParameter p in paraValues)
                {
                    DbCommand.Parameters.Add(p);
                }
            }
        }

        //清除参数
        private void ClearParameters()
        {
            DbCommand.Parameters.Clear();
        }

        /// <summary>
        /// 执行 SQL 方法委托
        /// </summary>
        /// <typeparam name="T">返回值类型</typeparam>
        /// <returns></returns>
        private delegate T ExecuteHandler<T>();

        #endregion

        #region ExecuteReader

        /// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="cmdText">SQL 语句</param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string cmdText)
        {
            return ExecuteReader(CommandType.Text, cmdText, null);
        }

        /// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="cmdType">SQL 类型</param>
        /// <param name="cmdText">SQL 语句或存储过程</param>
        /// <param name="paraValues">参数</param>
        /// <returns></returns>
        public IDataReader ExecuteReader(CommandType cmdType, string cmdText, params IDataParameter[] paraValues)
        {
            PrepareCommand(cmdType, cmdText);
            PrepareParameters(paraValues);
            Open();
            IDataReader obj = DbCommand.ExecuteReader();
            ClearParameters();
            return obj;
        }

        #endregion

        #region FetchData

        /// <summary>
        /// 查找数据
        /// </summary>
        /// <param name="cmdText">SQL 语句</param>
        /// <returns></returns>
        public DataTable FetchData(string cmdText)
        {
            return FetchData(CommandType.Text, cmdText, null);
        }

        /// <summary>
        /// 查找数据
        /// </summary>
        /// <param name="cmdType">SQL 类型</param>
        /// <param name="cmdText">SQL 语句或存储过程</param>
        /// <param name="paraValues">参数</param>
        /// <returns></returns>
        public DataTable FetchData(CommandType cmdType, string cmdText, params IDataParameter[] paraValues)
        {
            PrepareCommand(cmdType, cmdText);
            PrepareParameters(paraValues);
            DataSet ds = new DataSet();
            DbDataAdapter.Fill(ds);
            ClearParameters();
            return ds.Tables[0];
        }

        /// <summary>
        /// 查找数据
        /// </summary>
        /// <param name="cmdTexts">SQL 语句</param>
        /// <returns></returns>
        public DataSet FetchData(string[] cmdTexts)
        {
            Dictionary<string, IDataParameter[]> dictSQL = new Dictionary<string, IDataParameter[]>();
            foreach (string text in cmdTexts)
            {
                dictSQL.Add(text, null);
            }
            return FetchData(CommandType.Text, dictSQL);
        }

        /// <summary>
        /// 查找数据
        /// </summary>
        /// <param name="cmdType">SQL 类型</param>
        /// <param name="dictSQL">SQL 语句或存储过程及其参数</param>
        /// <returns></returns>
        public DataSet FetchData(CommandType cmdType, Dictionary<string, IDataParameter[]> dictSQL)
        {
            DataSet ds = new DataSet();

            foreach (KeyValuePair<string, IDataParameter[]> obj in dictSQL)
            {
                PrepareCommand(cmdType, obj.Key);
                PrepareParameters(obj.Value);
                DbDataAdapter.Fill(ds);
                ClearParameters();
            }

            return ds;
        }

        #endregion

        #region CreateParameter

        /// <summary>
        /// 创建参数对象
        /// </summary>
        /// <returns></returns>
        public IDataParameter CreateParameter(string name, object value)
        {
            IDataParameter para = DbCommand.CreateParameter();
            para.ParameterName = name;
            para.Value = value;
            return para;
        }

        #endregion

        #region GetTableSchema

        /// <summary>
        /// 获取表结构信息
        /// </summary>
        /// <param name="tableName">数据库中的表名</param>
        /// <returns></returns>
        public DataTable GetTableSchema(string tableName)
        {
            string key = DbConnection.ConnectionString + tableName;
            if (dtSchema.ContainsKey(key))
            {
                return dtSchema[key];
            }
            else
            {
                DataSet ds = new DataSet();
                DbCommand.CommandText = string.Format("select * from {0} where 0=1", tableName);
                DbDataAdapter.FillSchema(ds, SchemaType.Mapped);
                DataTable dt = ds.Tables[0];
                dtSchema.Add(key, dt);
                return dt;
            }
        }

        #endregion

        #region IDisposable

        /// <summary>
        /// 关闭数据库
        /// <para>功能与Close()相同</para>
        /// </summary>
        public void Dispose()
        {
            this.Close();
        }

        #endregion
    }
}
